﻿CREATE PROCEDURE [maint].[RestoreFullFromDevice]
@DbName [sysname], @DeviceName [sysname], @FilePosition INT, @Recover BIT=0, @OutputDiagnostics BIT=0
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @RC int, @ErrorMessage nvarchar(max);
	SET @RC = 0;

	IF(@OutputDiagnostics = 1)
		BEGIN
			PRINT OBJECT_NAME(@@PROCID)
			PRINT N'	@DbName = N''' + ISNULL(@DbName,N'NULL') + ''''
			PRINT N'	@DeviceName = N''' + ISNULL(@DeviceName,N'NULL') + ''''
			PRINT N'	@FilePosition = N''' + ISNULL(CAST(@FilePosition AS nvarchar),N'NULL') + ''''
			PRINT N'	@Recover = N''' + ISNULL(CAST(@Recover AS nvarchar),N'NULL') + ''''
		END

	BEGIN TRY
	
		DECLARE @FileListTableName nvarchar(36)
		EXEC @RC = [maint].[GetRestoreFileList] @DeviceName, @FileListTableName OUTPUT, @OutputDiagnostics;

		DECLARE @SqlDataRoot nvarchar(256), @SqlLogRoot nvarchar(256)
		EXEC @RC = [util].[GetDefaultDataPath] @SqlDataRoot OUTPUT;
		EXEC @RC = [util].[GetDefaultLogPath] @SqlLogRoot OUTPUT;

		DECLARE @MoveStatement nvarchar(1024), @MoveSql nvarchar(4000);
		SELECT @MoveStatement = 
			N'SELECT @MoveSql = (
				SELECT 
					(
						CHAR(10)
						+ '',MOVE ''''''
						+ [LogicalName]
						+ '''''' TO ''''''
						+ (CASE [Type]
							WHEN ''D'' THEN ''' + @SqlDataRoot + '\''
							ELSE ''' + @SqlLogRoot + '\''
						END)
						+ REPLACE(''' + @DbName + ''','''''''',''_'')
						+ ''_''
						+ REPLACE([LogicalName],'''''''',''_'')
						+ (CASE [Type]
							WHEN ''D'' THEN ''.MDF''
							ELSE ''.LDF''
						END)
						+ ''''''''
					) AS ''text()'' FROM ' + @FileListTableName + N'
				FOR XML PATH('''')
			)';

		IF(@OutputDiagnostics = 1)
			PRINT '	@MoveStatement = ' + CHAR(10) + ISNULL(@MoveStatement,N'NULL') + CHAR(10);

		EXEC @RC = sp_executesql @MoveStatement
			,N'@MoveSql nvarchar(4000) OUTPUT'
			,@MoveSql OUTPUT

		DECLARE @FullRestoreStatement nvarchar(1024)
		SET @FullRestoreStatement = 
			N'RESTORE DATABASE [' + @DbName + ']
			FROM [' + @DeviceName + ']
			WITH 
			FILE = ' + CAST(@FilePosition AS nvarchar)
			+ @MoveSql
			+ CHAR(10)
			+ (CASE
				WHEN @Recover = 1 THEN ', RECOVERY'
				ELSE ', NORECOVERY'
			END)
			+ ', REPLACE, STATS = 10';

		IF(@OutputDiagnostics = 1)
			PRINT '	@FullRestoreStatement = ' + CHAR(10) + ISNULL(@FullRestoreStatement,N'NULL') + CHAR(10)

		DECLARE @StartDate datetime
		SET @StartDate = GETDATE();
		
		EXEC(@FullRestoreStatement)

	END TRY
	BEGIN CATCH
		SET @RC = ERROR_NUMBER();
		EXEC [util].[GetErrorOutput] @Output = @ErrorMessage OUTPUT;
	END CATCH

	DECLARE @FinishDate datetime
	SET @FinishDate = GETDATE();
	
	EXEC @RC = [maint].[LogRestore]
		 @DbName = @DbName
		,@DeviceName = @DeviceName
		,@FilePosition = @FilePosition
		,@Type = 1
		,@StartDate = @StartDate
		,@FinishDate = @FinishDate
		,@RecoverStatus = @Recover
		,@ErrorCode = @RC
		,@Output = @ErrorMessage
		,@OutputDiagnostics = @OutputDiagnostics;

	RETURN(@RC)
END;